﻿USE [master]
GO
CREATE DATABASE [xRepair]
GO
ALTER DATABASE [xRepair] MODIFY FILE
( NAME = N'xRepair' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO
ALTER DATABASE [xRepair] MODIFY FILE
( NAME = N'xRepair_log', SIZE = 1024KB , MAXSIZE = 51200KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [xRepair] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [xRepair].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [xRepair] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [xRepair] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [xRepair] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [xRepair] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [xRepair] SET ARITHABORT OFF 
GO
ALTER DATABASE [xRepair] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [xRepair] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [xRepair] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [xRepair] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [xRepair] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [xRepair] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [xRepair] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [xRepair] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [xRepair] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [xRepair] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [xRepair] SET  DISABLE_BROKER 
GO
ALTER DATABASE [xRepair] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [xRepair] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [xRepair] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [xRepair] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [xRepair] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [xRepair] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [xRepair] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [xRepair] SET RECOVERY FULL 
GO
ALTER DATABASE [xRepair] SET  MULTI_USER 
GO
ALTER DATABASE [xRepair] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [xRepair] SET DB_CHAINING OFF 
GO
EXEC sys.sp_db_vardecimal_storage_format N'xRepair', N'ON'
GO
USE [xRepair]
GO
/****** Object:  StoredProcedure [dbo].[AddNewService]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AddNewService]
		@SymptomTypeID			INT
		,@ServiceName			NVARCHAR(500)
		,@ServiceDescription	NVARCHAR(MAX)
		,@LaborCost				FLOAT
		,@DiscountLabor			FLOAT
		,@PartCost				FLOAT
		,@DiscountPart			FLOAT		
		,@NewIdentity			INT = NULL OUTPUT
AS
BEGIN

	SET NOCOUNT ON;
	
	INSERT INTO [dbo].[Services]
           ([SymptomTypeID]
           ,[ServiceName]
           ,[ServiceDescription]
           ,[LaborCost]
           ,[DiscountLabor]
           ,[PartCost]
           ,[DiscountPart]
           ,[IsActive])
     VALUES
           (@SymptomTypeID
           ,@ServiceName
           ,@ServiceDescription
           ,@LaborCost
           ,@DiscountLabor
           ,@PartCost
           ,@DiscountPart
           ,1)

	SELECT @NewIdentity = @@IDENTITY

END

GO
/****** Object:  StoredProcedure [dbo].[AddNewSymptom]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AddNewSymptom]
	 @Name				NVARCHAR(200)
    ,@Description		NVARCHAR(MAX)
	,@DeviceTypeID		INT		
	,@NewIdentity		INT = NULL OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

    INSERT INTO [dbo].[SymptomTypes]
           ([Name]
           ,[Description]
		   ,[DeviceTypeID]
           ,[IsActive])
     VALUES
           (@Name
           ,@Description
		   ,@DeviceTypeID
           ,1)

	SELECT @NewIdentity = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[DeactiveServiceByID]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeactiveServiceByID]
	@ID					INT,
	@Rowcount			INT = -1 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Services]
	SET [IsActive] = 0
	WHERE [ID] = @ID

	SELECT @Rowcount = @@ROWCOUNT
END

GO
/****** Object:  StoredProcedure [dbo].[DeactiveSymptomByID]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeactiveSymptomByID]
	@ID					INT,
	@Rowcount			INT = -1 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

    UPDATE [dbo].[SymptomTypes]
	SET [IsActive] = 0
	WHERE [ID] = @ID

	SELECT @Rowcount = @@ROWCOUNT
END

GO
/****** Object:  StoredProcedure [dbo].[GetAllActiveServices]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetAllActiveServices]	
AS
BEGIN

	SET NOCOUNT ON;

	SELECT 
		[ID]		
		,[SymptomTypeID]
		,[ServiceName]
		,[ServiceDescription]
		,[LaborCost]
		,[DiscountLabor]
		,[PartCost]
		,[DiscountPart]
	FROM [xRepair].[dbo].[Services] WITH(NOLOCK)
	WHERE [IsActive] = 1 OR [IsActive] IS NULL

END

GO
/****** Object:  StoredProcedure [dbo].[GetAllActiveSymptoms]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetAllActiveSymptoms]
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT 
		[ID]
		,[Name]
		,[Description]
		,[DeviceTypeID]
	FROM [xRepair].[dbo].[SymptomTypes] WITH(NOLOCK)
	WHERE [IsActive] = 1 OR [IsActive] IS NULL
END

GO
/****** Object:  StoredProcedure [dbo].[UpdateService]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateService]
		@ID						INT		
		,@SymptomTypeID			INT
		,@ServiceName			NVARCHAR(500)
		,@ServiceDescription	NVARCHAR(MAX)
		,@LaborCost				FLOAT
		,@DiscountLabor			FLOAT
		,@PartCost				FLOAT
		,@DiscountPart			FLOAT	
		,@IsActive				BIT	
		,@Rowcount				INT = -1 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Services]
	SET [SymptomTypeID] = @SymptomTypeID
		,[ServiceName] = @ServiceName
		,[ServiceDescription] = @ServiceDescription
		,[LaborCost] = @LaborCost
		,[DiscountLabor] = @DiscountLabor
		,[PartCost] = @PartCost
		,[DiscountPart] = @DiscountPart
		,[IsActive] = @IsActive
	WHERE [ID] = @ID

    SELECT @Rowcount = @@ROWCOUNT
END

GO
/****** Object:  StoredProcedure [dbo].[UpdateSymptom]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateSymptom]
	 @ID				INT	
	,@Name				NVARCHAR(200)
	,@Description		NVARCHAR(MAX)	
	,@DeviceTypeID		INT
	,@IsActive			BIT	
	,@Rowcount			INT = -1 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[SymptomTypes]
	SET [Name] = @Name
		,[Description] = @Description
		,[DeviceTypeID] = @DeviceTypeID
		,[IsActive] = @IsActive
	WHERE [ID] = @ID

    SELECT @Rowcount = @@ROWCOUNT
END

GO
/****** Object:  Table [dbo].[DeviceTypes]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeviceTypes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Type] [int] NOT NULL,
	[Brand] [nvarchar](200) NOT NULL,
	[Model] [nvarchar](200) NOT NULL,
	[Version] [nvarchar](50) NOT NULL,
	[Manufacturer] [nvarchar](500) NULL,
 CONSTRAINT [PK_DeviceTypes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Orders]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NOT NULL,
	[CreatedAt] [datetime] NOT NULL,
	[CustomerDescription] [nvarchar](max) NULL,
	[ServiceID] [int] NOT NULL,
	[TechnicianID] [int] NOT NULL,
	[AcceptedAt] [datetime] NOT NULL,
	[TechnicianDescription] [nvarchar](max) NULL,
	[DueDate] [datetime] NULL,
	[LastUpdatedAt] [datetime] NULL,
	[LastUpdatedBy] [int] NULL,
	[StatusID] [int] NULL,
	[ExtraFee] [float] NULL,
	[ExtraFeeNote] [nvarchar](2000) NULL,
	[BillingAddress] [nvarchar](max) NULL,
	[ShippingAddress] [nvarchar](max) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[OrderStatus]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderStatus](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_OrderStatus] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Services]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Services](
	[ID] [int] IDENTITY(1,1) NOT NULL,	
	[SymptomTypeID] [int] NOT NULL,
	[ServiceName] [nvarchar](500) NULL,
	[ServiceDescription] [nvarchar](max) NULL,
	[LaborCost] [float] NULL,
	[DiscountLabor] [float] NULL,
	[PartCost] [float] NULL,
	[DiscountPart] [float] NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[SymptomTypes]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SymptomTypes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[DeviceTypeID] [int] NOT NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_SymptomTypes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[UserRoles]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](2000) NULL,
 CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Users]    Script Date: 2/16/2014 2:33:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RoleID] [int] NOT NULL,
	[Username] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](200) NOT NULL,
	[Salt] [varchar](64) NULL,
	[Alias] [nvarchar](200) NULL,
	[CreatedAt] [datetime] NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[LastUpdatedAt] [datetime] NULL,
	[LastUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[DeviceTypes] ON 

GO
INSERT [dbo].[DeviceTypes] ([ID], [Type], [Brand], [Model], [Version], [Manufacturer]) VALUES (2, 1, N'Apple', N'iPhone 3G', N'3', NULL)
GO
INSERT [dbo].[DeviceTypes] ([ID], [Type], [Brand], [Model], [Version], [Manufacturer]) VALUES (3, 1, N'Apple', N'iPhone 4G', N'4', NULL)
GO
INSERT [dbo].[DeviceTypes] ([ID], [Type], [Brand], [Model], [Version], [Manufacturer]) VALUES (4, 1, N'Apple', N'iPhone 4S', N'4', NULL)
GO
INSERT [dbo].[DeviceTypes] ([ID], [Type], [Brand], [Model], [Version], [Manufacturer]) VALUES (5, 2, N'Samsung', N'Galaxy S5', N'5', NULL)
GO
INSERT [dbo].[DeviceTypes] ([ID], [Type], [Brand], [Model], [Version], [Manufacturer]) VALUES (6, 2, N'LG', N'Optimus', N'4', NULL)
GO
SET IDENTITY_INSERT [dbo].[DeviceTypes] OFF
GO
SET IDENTITY_INSERT [dbo].[Orders] ON 

GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (1, 5, CAST(0x0000A13A00000000 AS DateTime), NULL, 1, 3, CAST(0x0000A1C400000000 AS DateTime), NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (2, 6, CAST(0x0000A1F700000000 AS DateTime), NULL, 2, 3, CAST(0x0000A21400000000 AS DateTime), NULL, NULL, NULL, NULL, 2, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (3, 7, CAST(0x0000A13D00000000 AS DateTime), NULL, 3, 11, CAST(0x0000A19600000000 AS DateTime), NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (5, 8, CAST(0x0000A15900000000 AS DateTime), NULL, 4, 4, CAST(0x0000A1B500000000 AS DateTime), NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (6, 9, CAST(0x0000A17900000000 AS DateTime), NULL, 5, 4, CAST(0x0000A1B500000000 AS DateTime), NULL, NULL, NULL, NULL, 5, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (7, 10, CAST(0x0000A13A00000000 AS DateTime), NULL, 6, 11, CAST(0x0000A18200000000 AS DateTime), NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Orders] ([ID], [CustomerID], [CreatedAt], [CustomerDescription], [ServiceID], [TechnicianID], [AcceptedAt], [TechnicianDescription], [DueDate], [LastUpdatedAt], [LastUpdatedBy], [StatusID], [ExtraFee], [ExtraFeeNote], [BillingAddress], [ShippingAddress]) VALUES (9, 12, CAST(0x0000A1BF00000000 AS DateTime), NULL, 7, 4, CAST(0x0000A21500000000 AS DateTime), NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO
SET IDENTITY_INSERT [dbo].[OrderStatus] ON 

GO
INSERT [dbo].[OrderStatus] ([ID], [Description]) VALUES (1, N'Completed')
GO
INSERT [dbo].[OrderStatus] ([ID], [Description]) VALUES (2, N'Waiting components')
GO
INSERT [dbo].[OrderStatus] ([ID], [Description]) VALUES (3, N'Cannot fix')
GO
INSERT [dbo].[OrderStatus] ([ID], [Description]) VALUES (4, N'In waiting list')
GO
INSERT [dbo].[OrderStatus] ([ID], [Description]) VALUES (5, N'Fixing')
GO
SET IDENTITY_INSERT [dbo].[OrderStatus] OFF
GO
SET IDENTITY_INSERT [dbo].[Services] ON 

GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (1, 1, N'Auto reboot', N'Automatic reboot and loss of all data', 0.2, 0.1, 0.1, 0.3, 1)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (2, 2, N'low resolution', N'Very low resolution picture showing on the screen when a contact calls you', 0.1, 0.1, 0.1, 0.1, 1)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (3, 4, N'Wrong zoom', N'most icons are zoomed in and pixelated', 0.1, 0.15, 0.1, 0.1, 1)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (4, 5, N'not charge', N'can not charge', 0.1, 0.1, 0.1, 0.1, 0)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (5, 6, N'wireless problem', N'Weak or lost Weak or lost wireless connections', 0.1, 0.1, 0.1, 0.1, 1)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (6, 7, N'camera problem', N'Rear camera white balance doesn’t work', 0.1, 0.1, 0.1, 0.1, NULL)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (7, 3, N'Email problem', N'Email notification sounds laggy/distorted', 0.1, 0.1, 0.1, 0.1, NULL)
GO
INSERT [dbo].[Services] ([ID], [SymptomTypeID], [ServiceName], [ServiceDescription], [LaborCost], [DiscountLabor], [PartCost], [DiscountPart], [IsActive]) VALUES (9, 2, N'test', N'hehehe', 0.1, 0.1, 0.1, 0.6, 1)
GO
SET IDENTITY_INSERT [dbo].[Services] OFF
GO
SET IDENTITY_INSERT [dbo].[SymptomTypes] ON 

GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (1, N'Auto reboot', N'Automatic reboot and loss of all data', 2, NULL)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (2, N'low resolution', N'Very low resolution picture showing on the screen when a contact calls you', 2, 0)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (3, N'Email problem', N'Email notification sounds laggy/distorted', 2, NULL)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (4, N'Wrong zoom', N'most icons are zoomed in and pixelated', 2, NULL)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (5, N'not charge', N'can not charge', 2, 0)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (6, N'wireless problem', N'Weak or lost Weak or lost wireless connections', 2, NULL)
GO
INSERT [dbo].[SymptomTypes] ([ID], [Name], [Description], [DeviceTypeID], [IsActive]) VALUES (7, N'camera problem', N'Rear camera white balance doesn’t work', 2, NULL)
GO
SET IDENTITY_INSERT [dbo].[SymptomTypes] OFF
GO
SET IDENTITY_INSERT [dbo].[UserRoles] ON 

GO
INSERT [dbo].[UserRoles] ([ID], [Description]) VALUES (1, N'Consultant')
GO
INSERT [dbo].[UserRoles] ([ID], [Description]) VALUES (2, N'Technician')
GO
INSERT [dbo].[UserRoles] ([ID], [Description]) VALUES (3, N'Customer')
GO
INSERT [dbo].[UserRoles] ([ID], [Description]) VALUES (4, N'Admin')
GO
SET IDENTITY_INSERT [dbo].[UserRoles] OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON 

GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (1, 1, N'avu3', N'123', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (2, 1, N'dle', N'244', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (3, 2, N'abc', N'544', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (4, 2, N'nam', N'5433', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (5, 3, N'tung', N'fasdf45', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (6, 3, N'lan', N'fer32', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (7, 3, N'toan', N'fasd4343', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (8, 3, N'hung', N'fasdfj54', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (9, 3, N'ngoc', N'oij4534', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (10, 3, N'binh', N'4354', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (11, 2, N'yen', N'4094', NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Users] ([ID], [RoleID], [Username], [Password], [Salt], [Alias], [CreatedAt], [CreatedBy], [LastUpdatedAt], [LastUpdatedBy]) VALUES (12, 3, N'bao', N'435jlj', NULL, NULL, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO
ALTER TABLE [dbo].[SymptomTypes]  WITH CHECK ADD  CONSTRAINT [FK_SymptomTypes_DeviceTypes] FOREIGN KEY([DeviceTypeID])
REFERENCES [dbo].[DeviceTypes] ([ID])
GO
ALTER TABLE [dbo].[SymptomTypes] CHECK CONSTRAINT [FK_SymptomTypes_DeviceTypes]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_OrderStatus] FOREIGN KEY([StatusID])
REFERENCES [dbo].[OrderStatus] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_OrderStatus]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Services] FOREIGN KEY([ServiceID])
REFERENCES [dbo].[Services] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Services]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Users] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Users1] FOREIGN KEY([TechnicianID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users1]
GO
ALTER TABLE [dbo].[Services]  WITH CHECK ADD  CONSTRAINT [FK_Services_SymptomTypes] FOREIGN KEY([SymptomTypeID])
REFERENCES [dbo].[SymptomTypes] ([ID])
GO
ALTER TABLE [dbo].[Services] CHECK CONSTRAINT [FK_Services_SymptomTypes]
GO
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_UserRoles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[UserRoles] ([ID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_UserRoles]
GO
USE [master]
GO
ALTER DATABASE [xRepair] SET  READ_WRITE 
GO
